********************************************************************************
*DYNAMIC IMPACTS OF PRICING GROUNDWATER
*Bruno, Jessoe, Hanemann in JAERE
********************************************************************************

clear all
capture log close
set more off

*SELECT OUTPUT DATE
global outputdate = "20231125"		 

*SET DIRECTORY
cd  "D:\Ellen\Dropbox\Pajaro_AgInnovation" 

log using "Submission\JAERE\Replication_Code\Log\Sumstat_log.log", replace

*CALL DATASET
use "Data\Parcel_Clean_yearrun_20230626.dta"
bysort year_run: gen nparcel = [_N]

*FILE CALLS QUARTERLY DATASET BELOW 

********************************************************************************
*FIGURE 12: PARCEL COUNT BY YEAR 
********************************************************************************
preserve
collapse (mean) nparcel, by (year_run)
twoway connected nparcel year_run if year_run <11, ylabel(600(50)800) xlabel(1(3)10) scheme(s1color) ytitle(Parcel Count) xtitle(Year) ///
tlabel(1 "2006" 4 "2009" 7 "2012" 10 "2015", gextend)
graph save "Figures\WellCount_$outputdate.gph", replace
graph export "Figures\WellCount_$outputdate.png", replace
restore

*Balance the panel
bysort parcelnum: gen ntime = [_N]
sum ntime
keep if ntime==`r(max)'

*Drop 2010
drop if year_run==5 

*How many parcels in dataset? 624 after balancing 
by parcelnum, sort: gen nvals_parcel = _n == 1 
preserve 
keep if inside==0
count if nvals_parcel
restore

preserve 
keep if inside==1
count if nvals_parcel
restore

egen N_parcelyear = count(nvals_parcel), by (parcelnum year_run)
bysort year_run parcelnum: gen wellcount=_N 

********************************************************************************
*Table 1: Summary Statistics ANNUAL WATER VARIABLES
********************************************************************************
*By DWZ:
eststo clear
bys inside: eststo: estpost tabstat extraction price delivered delivered_rate  CL_zonedate gw_depth if year_run <11, stat(count mean sd) col(stat)
esttab using "Tables\SumStat_extraction_annual_byDWZ_lim_$outputdate.tex", replace ///
nomtitles title("Descriptive Statistics") cells("count(fmt(0)) mean(fmt(2)) sd(fmt(2))") label 

*Aggregate:
********************************************************************************
eststo clear
estpost tabstat gw_depth extraction price delivered_rate delivered CL_zonedate flat_rate time_of_use rent_irrigated if year_run <11, ///
stat(count mean sd min max) col(stat)
esttab using "Tables\SumStat_extraction_annual_lim_$outputdate.tex", replace ///
nomtitles title("Descriptive Statistics") cells("count(fmt(0)) mean(fmt(2)) sd(fmt(2)) min(fmt(2)) max(fmt(2))") label 

********************************************************************************
********************************************************************************
use "Data\Parcel_Clean_20230626.dta", clear

preserve
*Balance the panel
bysort parcelnum: gen ntime = [_N]
sum ntime
keep if ntime==`r(max)'

*Drop 2010
drop if year ==2010
********************************************************************************
*Table: Summary Statistics QUARTERLY VARIABLES
********************************************************************************

*By DWZ
eststo clear
bys inside: eststo: estpost tabstat  extraction price delivered delivered_rate  CL_zonedate gw_depth if year<2016, ///
stat(count mean sd) col(stat)
esttab using "Tables\SumStat_extraction_quarterly_byDWZ_lim_$outputdate.tex", replace ///
nomtitles title("Descriptive Statistics") cells("count(fmt(0)) mean(fmt(2)) sd(fmt(2))") label 

*Aggregate
eststo clear
estpost tabstat gw_depth extraction price delivered_rate delivered CL_zonedate flat_rate time_of_use if year<2016, ///
stat(count mean sd min max) col(stat)
esttab using "Tables\SumStat_extraction_quarterly_lim_$outputdate.tex", replace ///
nomtitles title("Descriptive Statistics") cells("mean(fmt(2)) sd(fmt(2)) min(fmt(2)) max(fmt(2))") label 
restore
********************************************************************************
*FIGURE 3: Prices by zone over time LIMITED SAMPLE
********************************************************************************

twoway (connected price date if inside ==0 & date <222, sort lwidth(medthick) msymbol(medium) msize(small)) ///
(connected price date if inside==1 & date <222, sort lwidth(medium) msymbol(triangle) msize(vsmall)), ///
legend (label (1 Outside DWZ) label (2 Inside DWZ) rows(1)) ylabel(50(50)250) ///
scheme (s1color) ytitle("Groundwater Price ($/AF)") xline(202, lcolor(black) lpattern(dash)) ///
tlabel(180 "Q1/2005" 192 "Q1/2008" 204 "Q1/2011" 216 "Q1/2014", gextend)
graph save "Figures\PriceByYear_$outputdate.gph", replace
graph export "Figures\PriceByYear_$outputdate.png", width(4000) replace


********************************************************************************
*FIGURE 7: Energy price changes over time
********************************************************************************
preserve
keep if date>191
keep if date <224
twoway (connected flat_rate date, sort lwidth(medthick) msymbol(medium) msize(vsmall)) ///
(connected time_of_use date, sort lwidth(medium) msize(vsmall)), ///
scheme (s1color) ytitle("Electricity rates ($/kwh)") xline(202, lcolor(black) lpattern(dash)) ///
tlabel(192 "Q1/2008" 204 "Q1/2011" 216 "Q1/2014", gextend)
graph save "Figures\EnergyPriceByYear_$outputdate.gph", replace
graph export "Figures\EnergyPriceByYear_$outputdate.png", width(4000) replace
restore

********************************************************************************
*FIGURE 8: Average extraction by zone 
********************************************************************************		
preserve
collapse (mean) extraction if year<2016, by (inside date)
twoway (connected extraction date if inside==0, sort msize(small)) ///
(connected extraction date if inside==1, sort msize(small) lpattern(dash)),  ///
	ytitle(Mean Groundwater Extraction (AF)) xline(202, lcolor(black)) ///
	tlabel(180 "Q1/2005" 192 "Q1/2008" 204 "Q1/2011" 216 "Q1/2014" 224 "Q1/2016" , gextend) scheme(s1color) ///
legend (label (1 Outside DWZ) label (2 Inside DWZ) rows(1))
graph save "Figures\AvgExtraction_lim_$outputdate.gph", replace
graph export "Figures\AvgExtraction_lim_$outputdate.png", replace
restore

********************************************************************************
*FIGURE 11: Average Gross Water Use (Adding Recycled water deliveries)
********************************************************************************
preserve
collapse (mean) extraction (mean) delivered_percap if year<2016, by (inside date)
gen extract_plus = extraction + delivered_percap
twoway (connected extract_plus date if inside==0, sort msize(small)) ///
(connected extract_plus date if inside==1, sort msize(small) lpattern(dash)),  ///
	ytitle(Mean Extraction plus Recycled (AF)) xline(202, lcolor(black)) ///
	tlabel(180 "Q1/2005" 192 "Q1/2008" 204 "Q1/2011" 216 "Q1/2014" 224 "Q1/2016" , gextend) scheme(s1color) ///
legend (label (1 Outside DWZ) label (2 Inside DWZ) rows(1))
graph save "Figures\AvgExtraction_plus_lim_$outputdate.gph", replace
graph export "Figures\AvgExtraction_plus_lim_$outputdate.png", replace
restore


********************************************************************************
*FIGURE 10: Recycled Water Deliveries by Zone
********************************************************************************
preserve
collapse (median) delivered (mean) delivered_rate, by (inside date)
label var delivered "Recycled Deliveries"
label var delivered_rate "Rate"
keep if date < 224

twoway (connected delivered date if inside==0, sort msize(vsmall)) ///
(connected delivered date if inside==1, sort msize(vsmall)),  ///
	ytitle(Total Water Deliveries (AF)) ///
	tlabel(180 "Q1/2005" 192 "Q1/2008" 204 "Q1/2011" 216 "Q1/2014", gextend) scheme(s1color) ///
legend (label (1 Outside DWZ) label (2 Inside DWZ) rows(1))
graph save "Figures\Delivered_$outputdate.gph", replace
graph export "Figures\Delivered_$outputdate.png", width(4000) replace
restore

log close
